'use strict';

const MysqlClient = require('./mysql_client');
const loader = require('./sql_loader');
const helper = require('./db_helper');

const CLIENT = Symbol('CTXDB#CLINET');
const CONN = Symbol('CTXDB#CONNECTION');
const TRANS = Symbol('CTXDB#T');

class CTXDB {
  /**
   * @param {Egg.Application} app app
   * @param {boolean} is_transaction 是否作为事务
   */
  constructor(app, is_transaction = false) {
    helper.init(app);
    this.app = app;
    this.is_transaction = is_transaction;
    this[CLIENT] = {};
    this[CONN] = {};
  }

  // #region 事务相关
  /**
   * 事务实例
   * @return {CTXDB} transaction
   */
  get t() {
    if (this.is_transaction) return undefined;
    if (!this[TRANS]) {
      this[TRANS] = new CTXDB(this.app, true);
    }
    return this[TRANS];
  }

  /**
   * 事务提交
   * @param {['master'|'mysql2']} ds 数据源数组
   */
  async commit(ds = undefined) {
    if (!this.is_transaction) return;

    if (!ds) {
      ds = [];
      for (const _ds in this.app.config.mysql.clients) {
        ds.push(_ds);
      }
    }

    // 提交
    const commits = [];
    for (const _ds of ds) {
      if (this[CONN][_ds]) {
        commits.push(...this[CONN][_ds].map(v => v.commit()));
        delete this[CONN][_ds];
      }
    }
    await Promise.all(commits);

    // 释放
    const releases = [];
    for (const _ds of ds) {
      if (this[CLIENT][_ds]) {
        releases.push(...this[CLIENT][_ds].map(v => v.release()));
        delete this[CLIENT][_ds];
      }
    }
    await Promise.all(releases);
  }

  /**
   * 事务回滚
   * @param {['master'|'mysql2']} ds 数据源数组
   */
  async rollback(ds = undefined) {
    if (!this.is_transaction) return;

    if (!ds) {
      ds = [];
      for (const _ds in this.app.config.mysql.clients) {
        ds.push(_ds);
      }
    }

    // 回滚
    const rollbacks = [];
    for (const _ds of ds) {
      if (this[CONN][_ds]) {
        rollbacks.push(...this[CONN][_ds].map(v => v.rollback()));
        delete this[CONN][_ds];
      }
    }
    await Promise.all(rollbacks);

    // 释放
    const releases = [];
    for (const _ds of ds) {
      if (this[CLIENT][_ds]) {
        releases.push(...this[CLIENT][_ds].map(v => v.release()));
        delete this[CLIENT][_ds];
      }
    }
    await Promise.all(releases);
  }

  /**
   * 在事务堆栈中压入一个新的事务, 用于之后对应的事务使用
   * @param {'master'|'mysql2'} ds 数据源
   */
  async push(ds = undefined) {
    if (!this.is_transaction) return;
    ds = ds || this.app.config.mysql.default;
    let client_stack = this[CLIENT][ds];
    if (!client_stack) {
      this[CLIENT][ds] = [];
      this[CONN][ds];
      client_stack = this[CLIENT][ds];
    }
    const conn_stack = this[CONN][ds];
    const conn = await helper.getConnection(ds);
    conn.beginTransaction();
    const client = new MysqlClient(conn, this.app.logger);
    client_stack.push(client);
    conn_stack.push(conn);
  }

  /**
   * 弹出事务堆栈中的最后一个事务
   * @param {'commit'|'rollback'} r 移除后的操作
   * @param {'master'|'mysql2'} ds 数据源
   */
  async pop(r = 'commit', ds = undefined) {
    if (!this.is_transaction) return;
    ds = ds || this.app.config.mysql.default;
    const client_stack = this[CLIENT][ds];
    const conn_stack = this[CONN][ds];
    if (!client_stack || client_stack.length < 1) return;
    await conn_stack.pop()[r](); // 操作
    await client_stack.pop().release(); // 释放
  }

  // #endregion

  // #region 通用函数
  /**
   * 获取一个client对象
   * @param {'master'|'mysql2'} ds 数据源
   * @return {Promise<MysqlClient>} client sql执行实例
   */
  async getClient(ds = undefined) {
    ds = ds || this.app.config.mysql.default;
    // 事务实例, 获取实例的client
    if (this.is_transaction) {
      let client_stack = this[CLIENT][ds];
      if (!client_stack || client_stack.length < 1) {
        // 创建事务对象
        const conn = await helper.getConnection(ds);
        conn.beginTransaction();
        const client = new MysqlClient(conn, this.app.logger);
        this[CLIENT][ds] = [];
        this[CLIENT][ds].push(client);
        this[CONN][ds] = [];
        this[CONN][ds].push(conn);
        client_stack = this[CLIENT][ds];
      }
      const c = client_stack[client_stack.length - 1]; // 给出最后一个client
      return c;
    }
    // 非事务实例, 给出一个一次性的client
    const conn = await helper.getConnection(ds);
    return new MysqlClient(conn, this.app.logger);
  }

  /**
   * 根据SQL ID执行SQL
   * @param {string} sqlid SQL ID
   * @param {*} data 数据
   * @param {'master'|'mysql2'} ds 数据源 该参数会覆盖yml文件内的ds设置
   */
  async execute(sqlid, data, ds = undefined) {
    const cfg = loader.load(sqlid);
    if (!cfg) {
      throw new Error('Cannot find sql: ' + sqlid);
    }
    ds = ds || cfg.ds;
    const client = await this.getClient(ds);
    let result = null;
    try {
      result = await client.execute(sqlid, data);
    } catch (err) {
      if (!this.is_transaction) client.release();
      throw err;
    }
    if (!this.is_transaction) client.release();
    return result;
  }

  /**
   * 直接执行SQL
   * @param {string} sql SQL
   * @param {Object} data 数据
   * @param {'master'|'mysql2'} ds 数据源
   */
  async executeSql(sql, data, ds = undefined) {
    const client = await this.getClient(ds);
    let result = null;
    try {
      result = await client.executeSql(sql, data);
    } catch (err) {
      if (!this.is_transaction) client.release();
      throw err;
    }
    if (!this.is_transaction) client.release();
    return result;
  }

  /**
   * 执行SQL，参数为数组的方式
   * @param {string} sql SQL
   * @param {Object} datas 数据
   * @param {'master'|'mysql2'} ds 数据源
   */
  async query(sql, datas, ds = undefined) {
    const client = await this.getClient(ds);
    let result = null;
    try {
      result = await client.query(sql, datas);
    } catch (err) {
      if (!this.is_transaction) client.release();
      throw err;
    }
    if (!this.is_transaction) client.release();
    return result;
  }

  /**
   * 创建
   * @param {*} table 表名称
   * @param {*} record 待插入的数据对象
   * @param {*} config 其他配置
   * * ds 数据源
   */
  async create(table, record = {}, config = {}) {
    record = helper.columnFilter(table, record);
    const sql = 'INSERT INTO ' + table + ' SET ${record}';
    return this.executeSql(sql, { record }, config.ds);
  }


  /**
   * 批量创建
   * @param {*} table 表名称
   * @param {*} records 待插入的数据对象列表
   * @param {*} config 其他配置
   * * ds 数据源
   */
  async bulkCreate(table, records = [], config = {}) {
    if (records.length === 0) {
      return;
    }
    records = helper.columnFilter(table, records);
    const keys = Object.keys(records[0]);
    const values = records.map(record => {
      return keys.map(key => {
        return record[key];
      });
    });
    const sql = `INSERT INTO ${table} (${keys.join(',')}) VALUES ?`;
    return this.query(sql, [ values ], config.ds);
  }

  /**
   * 更新
   *
   * @param {*} table 表名称
   * @param {*} record 待插入的数据对象列表
   * @param {*} config 其他配置
   * * ds     数据源
   * * where  筛选条件对象
   */
  async update(table, record = {}, config = {}) {
    const where = config.where || {};
    const updateCols = [];
    const params = [];

    record = helper.columnFilter(table, record);
    for (const item in record) {
      updateCols.push(`${item} = ?`);
      params.push(record[item]);
    }

    const whereStr = helper.explainWhere(where, config.ds);
    const sql = `UPDATE ${table} SET ${updateCols.join(', ')} ${whereStr}`;
    return this.query(sql, params, config.ds);
  }

  /**
   * 删除
   *
   * @param {*} table 表名称
   * @param {*} config 其他配置
   * * ds 数据源
   * * where  筛选条件对象
   */
  async delete(table, config = {}) {
    const where = config.where || {};

    const whereStr = helper.explainWhere(where, config.ds);
    const sql = `DELETE FROM ${table} ${whereStr}`;
    return this.query(sql, [], config.ds);
  }


  /**
   * 单表查询所有记录
   *
   * @param {*} table 表名称
   * @param {*} config 其他配置
   * * ds       数据源
   * * where    筛选条件对象，object格式
   * * columns  要查询的字段，不填查所有字段，数组格式
   * * limit    limit限定值，字符串格式
   * * order    排序方式，字符串格式
   * * group    分组方式，字符串格式
   * @return {Promise<[]>} rows
   */
  async findAll(table, config = {}) {
    const where = config.where || {};
    const columns = config.columns || [];
    let limitStr = '';
    let orderBy = '';
    let groupBy = '';

    if (config.limit) {
      limitStr = ` LIMIT ${config.limit}`;
    }
    if (config.order) {
      orderBy = ` ORDER BY ${config.order}`;
    }
    if (config.group) {
      groupBy = ` GROUP BY ${config.group}`;
    }

    let columnStr = '*';
    if (columns.length > 0) {
      columnStr = `${columns.join(',')}`;
    }

    const whereStr = helper.explainWhere(where, config.ds);
    const sql = `SELECT ${columnStr} FROM ${table} ${whereStr} ${groupBy} ${orderBy} ${limitStr}`;

    return this.query(sql, [], config.ds);
  }

  /**
   * 单表查询一条记录
   *
   * @param {*} table 表名称
   * @param {*} config 其他配置
   * * ds       数据源
   * * where    筛选条件对象，object格式
   * * columns  要查询的字段，不填查所有字段，数组格式
   * @return {Promise<*>} row
   */
  async findOne(table, config = {}) {
    const records = await this.findAll(table, { ...config, ...{ limit: '1' } });
    if (records.length > 0) {
      return records[0];
    }
    return null;
  }

  /**
   * 单表统计满足条件的行数
   *
   * @param {*} table 表名称
   * @param {*} config 其他配置
   * * ds       数据源
   * * where    筛选条件对象，object格式
   * @return {Promise<number>} count
   */
  async count(table, config = {}) {
    const where = config.where || {};

    const whereStr = helper.explainWhere(where, config.ds);
    const sql = `SELECT COUNT(*) AS cnt FROM ${table} ${whereStr}`;

    const records = await this.query(sql, [], config.ds);
    return records[0].cnt;
  }

  // #endregion
}

CTXDB.P = helper.P;

module.exports = CTXDB;
